package com.wanmait.shoppingsys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.wanmait.shoppingsys.util.DbHelper;
import com.wanmait.shoppingsys.util.ManagePager;
import com.wanmait.shoppingsys.util.Pager;
import com.wanmait.shoppingsys.vo.City;
import com.wanmait.shoppingsys.vo.Specialty;
import com.wanmait.shoppingsys.vo.Type;
import com.wanmait.shoppingsys.vo.Admin;
import com.wanmait.shoppingsys.vo.Province;





public class SpecialtyDAO {
		
		private static SpecialtyDAO specialtyDAO;
		public static SpecialtyDAO getDAO(){
			if(specialtyDAO==null) {
				specialtyDAO=new SpecialtyDAO();
			}
			return specialtyDAO;
		}
		
		private SpecialtyDAO(){};
		
		/**
		 * 工具方法，其他查找方法调用这个方法来完成
		 * @param sql
		 * @param params
		 * @return
		 */
		private  List<Specialty> findBySql(String sql,List params){
			ArrayList<Specialty> specilatys = new ArrayList<Specialty>();
			
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				for(int i=0;params!=null&&i<params.size();i++) {
					ps.setObject(i+1, params.get(i));
				}
				rs = ps.executeQuery();
				while(rs.next()) {
					Specialty specialty=new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));
					specialty.setIntroduce(rs.getString("introduce"));
					
					specialty.setInventory(rs.getInt("inventory"));
					specialty.setPrice(rs.getDouble("price"));
					
					specialty.setPic(rs.getString("pic"));
					specialty.setPutawayTime(rs.getTimestamp("putaway_time"));
					
					
					City city=new City();
					city.setId(rs.getInt("city_id"));
					city.setName(rs.getString("name"));
					city.setIntroduce(rs.getString("introduce"));
					
					
					Admin admin=new Admin();
					admin.setId(rs.getInt("admin_id"));
					admin.setName(rs.getString("name"));
			
					
					Type type=new Type();
					type.setId(rs.getInt("id"));
				
					
					specialty.setCity(city);
					specialty.setAdmin(admin);
					specialty.setType(type);
					
					specilatys.add(specialty);

				}
				
			} catch (SQLException e) {
				
				e.printStackTrace();
			}finally {
				DbHelper.close(rs, ps, conn);
			}
			return specilatys;
		}
		
		/**
		   *  通过城市查找
		 * @param name
		 * @param cityId
		 * @return
		 */
		public List<Specialty> findbycity(String name,Integer cityId)
		{
			String sql="SELECT specialty.*,city.name" + 
					"FROM  specialty LEFT JOIN city ON specialty.city_id=city.id" + 
					"WhERE city.id=?";
			ArrayList params = new ArrayList();
			params.add(cityId);
			List<Specialty> specialtys = this.findBySql(sql, params);
			return specialtys;
		}
		
		public List<Specialty> findAll(){
			String sql="SELECT specialty.*,type.kind AS type_kind FROM specialty LEFT JOIN type ON specialty.type_id=type.id";
			return this.findBySql(sql, null);
		}

		
		public void insert(Specialty specialty)//增加商品
		{
			String sql="INSERT INTO specialty(name,type_id,price,introduce,pic,city_id,admin_id) VALUES(?,?,?,?,?,?,?)";
			ArrayList params=new ArrayList();
			params.add(specialty.getName());
			params.add(specialty.getType().getId());
			params.add(specialty.getPrice());
			params.add(specialty.getIntroduce());
			params.add(specialty.getPic());
			params.add(specialty.getCity().getId());
			params.add(specialty.getAdminId());
			DbHelper.update(sql,params);
		}
		
		public void delete(Integer id) {
			String sql = "DELETE FROM  specialty WHERE id=?";
			ArrayList params = new ArrayList();
			params.add(id);
			DbHelper.update(sql,params);
		}
		
		public void update(Specialty specialty) {//更改商品信息
			String sql = "UPDATE specialty SET name=?,type_id=?,price=?,introduce=?,pic=?,city_id=? where id=?";
			ArrayList params = new ArrayList();
			
			params.add(specialty.getName());
			params.add(specialty.getType().getId());
			params.add(specialty.getPrice());
			params.add(specialty.getIntroduce());
			params.add(specialty.getPic());
			params.add(specialty.getCity().getId());
			params.add(specialty.getId());
			DbHelper.update(sql,params);
		}
		
		public void update1(Integer id,Integer en) {//逻辑删除商品
			String sql = "UPDATE specialty SET enable=? where id=?";
			ArrayList params = new ArrayList();
			params.add(en);
			params.add(id);
			DbHelper.update(sql,params);
		}

		public void update2(Integer id) {//逻辑删除商品
			String sql = "UPDATE specialty SET enable=1 where id=?";
			ArrayList params = new ArrayList();
			params.add(id);
			DbHelper.update(sql,params);
		}
		

		public List<Specialty> findAllByCityId(Integer cityId){
			String sql="SELECT specialty.*,city.name AS city_name "
					+ "FROM specialty "
					+ "LEFT JOIN city ON specialty.city_id=city.id "
					+ "WHERE specialty.city_id = ?";
			ArrayList params=new ArrayList();
			params.add(cityId);
			List<Specialty> specialtys=this.findBySql(sql, params);
			return specialtys;
		}

		public List<Specialty> findAllByProvinceId(Integer provinceId){
			String sql="SELECT specialty.*,province.name As province_name" + 
					"FROM specialty" + 
					"LEFT JOIN city ON city.id=specialty.city_id" + 
					"LEFT JOIN province ON province.id=city.province_id" + 
					"WHERE city.province_id=?";
			ArrayList params=new ArrayList();
			params.add(provinceId);
			List<Specialty> specialtys=this.findBySql(sql, params);
			return specialtys;
		}
		
		
		public int getDataCount(String title) { //关键字查找个数
			int count = 0;
			String sql = "select count(*) from specialty where name like ?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1,"%"+title+"%");
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
		
		
		public Pager produitSelect(Pager pager)     //搜索根据关键字查找方法
		{
			ArrayList<Specialty> specialtys = new ArrayList<>();
			pager.setPageDate(specialtys);
			pager.setRowCount(this.getDataCount(pager.getProduit()));
			String sql="SELECT specialty.*,type.kind AS type_kind  from specialty LEFT JOIN type ON specialty.type_id=type.id  WHERE specialty.name like ? and specialty.enable = 1 LIMIT ?,?";
			Connection conn=DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1,"%"+pager.getProduit()+"%");
				ps.setInt(2, (pager.getPageNum()-1)*pager.getPageSize());
				ps.setInt(3, pager.getPageSize());
				rs = ps.executeQuery();
				while(rs.next())
				{
					Specialty specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));			  		//商品名称
					specialty.setIntroduce(rs.getString("introduce"));			//商品介绍
					specialty.setPic(rs.getString("pic"));                		//商品图片
					specialty.setPrice(rs.getDouble("price"));					//商品价格
					//specialty.setCityId(rs.getInt("cityId"));					//商品关联城市Id
					//specialty.setInventory(rs.getInt("inventory"));	
					Type type=new Type();
					type.setId(rs.getInt("type_id"));
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					
					
					specialtys.add(specialty);
					
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				DbHelper.close(rs,ps,conn);
			}
			return pager;
			
		}
		
		public Pager produitByPrice(Pager pager)     //搜索根据关键字查找按价格排序
		{
			ArrayList<Specialty> specialtys = new ArrayList<>();
			pager.setPageDate(specialtys);
			pager.setRowCount(this.getDataCount(pager.getProduit()));
			String sql="SELECT specialty.*,type.kind AS type_kind  from specialty LEFT JOIN type ON specialty.type_id=type.id  WHERE specialty.name like ? and specialty.enable = 1 order by specialty.price desc LIMIT ?,?";
			Connection conn=DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1,"%"+pager.getProduit()+"%");
				ps.setInt(2, (pager.getPageNum()-1)*pager.getPageSize());
				ps.setInt(3, pager.getPageSize());
				rs = ps.executeQuery();
				while(rs.next())
				{
					Specialty specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));			  		//商品名称
					specialty.setIntroduce(rs.getString("introduce"));			//商品介绍
					specialty.setPic(rs.getString("pic"));                		//商品图片
					specialty.setPrice(rs.getDouble("price"));					//商品价格
					//specialty.setCityId(rs.getInt("cityId"));					//商品关联城市Id
					//specialty.setInventory(rs.getInt("inventory"));	
					Type type=new Type();
					type.setId(rs.getInt("type_id"));
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					
					
					specialtys.add(specialty);
					
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				DbHelper.close(rs,ps,conn);
			}
			return pager;
			
		}
		
		
		public int getDataCount(Integer id) { //类型查找个数
			int count = 0;
			String sql = "select count(*) from specialty where type_id=?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
		
		
		
		public Pager findAll(Pager pager)     //按类型查询
		{
			Specialty specialty = null;
			ArrayList<Specialty> specialtys = new ArrayList<>();
			pager.setPageDate(specialtys);
			pager.setRowCount(this.getDataCount(pager.getId()));
			String sql="SELECT specialty.*,type.kind AS type_kind  from specialty LEFT JOIN type ON specialty.type_id=type.id WHERE specialty.type_id=? and specialty.enable = 1 LIMIT ?,?";
			Connection conn=DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, pager.getId());
				ps.setInt(2, (pager.getPageNum()-1)*pager.getPageSize());
				ps.setInt(3, pager.getPageSize());
				
				rs = ps.executeQuery();
				while(rs.next())
				{
					
					specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));			  		//商品名称
					specialty.setPic(rs.getString("pic"));                		//商品图片
					specialty.setPrice(rs.getDouble("price"));					//商品价格	
					
					Type type=new Type();
					type.setId(rs.getInt("type_id"));
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					
					specialtys.add(specialty);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				DbHelper.close(rs,ps,conn);
			}
			return pager;
			
		}
		public Pager typeByPrice(Pager pager)     //按类型查询价格排序
		{
			Specialty specialty = null;
			ArrayList<Specialty> specialtys = new ArrayList<>();
			pager.setPageDate(specialtys);
			pager.setRowCount(this.getDataCount(pager.getId()));
			String sql="SELECT specialty.*,type.kind AS type_kind  from specialty LEFT JOIN type ON specialty.type_id=type.id WHERE specialty.type_id=? and specialty.enable = 1 order by specialty.price desc LIMIT ?,?";
			Connection conn=DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, pager.getId());
				ps.setInt(2, (pager.getPageNum()-1)*pager.getPageSize());
				ps.setInt(3, pager.getPageSize());
				
				rs = ps.executeQuery();
				while(rs.next())
				{
					
					specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));			  		//商品名称
					specialty.setPic(rs.getString("pic"));                		//商品图片
					specialty.setPrice(rs.getDouble("price"));					//商品价格	
					
					Type type=new Type();
					type.setId(rs.getInt("type_id"));
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					
					specialtys.add(specialty);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}finally {
				DbHelper.close(rs,ps,conn);
			}
			return pager;
			
		}
		
		public Specialty findById(Integer id)   // 通过ID查询
		{
			Specialty specialty = null;
			String sql = "SELECT specialty.*,city.name as city_name,type.kind as type_kind from specialty left join city on city.id = specialty.city_id left join type on type.id = specialty.type_id WHERE specialty.id=?";
			Connection conn=DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs=null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, id);
				rs = ps.executeQuery();
				while(rs.next())
				{
					specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));			  		//商品名称
					specialty.setIntroduce(rs.getString("introduce"));			//商品介绍
					specialty.setPic(rs.getString("pic"));                		//商品图片
					specialty.setPrice(rs.getDouble("price"));                 //商品价格
					specialty.setTypeId(rs.getInt("type_id"));
					specialty.setPutawayTime(rs.getTimestamp("putaway_Time"));	//商品上架时间
					
					specialty.setInventory(rs.getInt("inventory"));     
					
					Type type=new Type();
					type.setId(rs.getInt("type_id"));
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					
					City city = new City();
					city.setId(rs.getInt("city_id"));
					city.setName(rs.getString("city_name"));
					
					specialty.setCity(city);
				}
		
			} catch (Exception e) {
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs,ps,conn);
			}
			return specialty;	
		}
		
public List<Specialty> findByName(String name,Integer typeId){
	String sql="SELECT specialty.*,type.kind AS type_title,province.name As province_name,city.name AS city_name"+
			"FROM specialty"+
			"LEFT JOIN type ON specialty.specialty_type_id=type.id"+
			"LEFT JOIN city ON city.id=specialty.city_id"+
			"LEFT JOIN province ON province.id=city.province_id"+
			"WHERE specialty.name like ? AND specialty.specialty_type_id=?";
	ArrayList params=new ArrayList();
	params.add("%"+name+"%");
	params.add(typeId);
	
	return this.findBySql(sql, params);
}

public void changeLike(Integer id){ //更改人气次数
	String sql="UPDATE specialty set likecount=likecount+1 WHERE id=?";
	ArrayList params=new ArrayList();
	params.add(id);
	DbHelper.update(sql, params);
}
public Pager produitByLike(Pager pager)     //搜索根据关键字查找按价格排序
{
	ArrayList<Specialty> specialtys = new ArrayList<>();
	pager.setPageDate(specialtys);
	pager.setRowCount(this.getDataCount(pager.getProduit()));
	String sql="SELECT specialty.*,type.kind AS type_kind  from specialty LEFT JOIN type ON specialty.type_id=type.id  WHERE specialty.name like ? and specialty.enable = 1 order by specialty.likecount desc LIMIT ?,?";
	Connection conn=DbHelper.getConn();
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		ps = conn.prepareStatement(sql);
		ps.setString(1,"%"+pager.getProduit()+"%");
		ps.setInt(2, (pager.getPageNum()-1)*pager.getPageSize());
		ps.setInt(3, pager.getPageSize());
		rs = ps.executeQuery();
		while(rs.next())
		{
			Specialty specialty = new Specialty();
			specialty.setId(rs.getInt("id"));
			specialty.setName(rs.getString("name"));			  		//商品名称
			specialty.setIntroduce(rs.getString("introduce"));			//商品介绍
			specialty.setPic(rs.getString("pic"));                		//商品图片
			specialty.setPrice(rs.getDouble("price"));					//商品价格
			Type type=new Type();
			type.setId(rs.getInt("type_id"));
			type.setKind(rs.getString("type_kind"));
			specialty.setType(type);
			
			
			specialtys.add(specialty);
			
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		DbHelper.close(rs,ps,conn);
	}
	return pager;
	
}

public Pager typeByLike(Pager pager)     //按类型查询
{
	Specialty specialty = null;
	ArrayList<Specialty> specialtys = new ArrayList<>();
	pager.setPageDate(specialtys);
	pager.setRowCount(this.getDataCount(pager.getId()));
	String sql="SELECT specialty.*,type.kind AS type_kind  from specialty LEFT JOIN type ON specialty.type_id=type.id WHERE specialty.type_id=? and specialty.enable = 1 order by specialty.likecount desc LIMIT ?,?";
	Connection conn=DbHelper.getConn();
	PreparedStatement ps = null;
	ResultSet rs = null;
	try {
		ps = conn.prepareStatement(sql);
		ps.setInt(1, pager.getId());
		ps.setInt(2, (pager.getPageNum()-1)*pager.getPageSize());
		ps.setInt(3, pager.getPageSize());
		
		rs = ps.executeQuery();
		while(rs.next())
		{
			
			specialty = new Specialty();
			specialty.setId(rs.getInt("id"));
			specialty.setName(rs.getString("name"));			  		//商品名称
			specialty.setPic(rs.getString("pic"));                		//商品图片
			specialty.setPrice(rs.getDouble("price"));					//商品价格	
			
			Type type=new Type();
			type.setId(rs.getInt("type_id"));
			type.setKind(rs.getString("type_kind"));
			specialty.setType(type);
			
			specialtys.add(specialty);
		}
	} catch (Exception e) {
		e.printStackTrace();
	}finally {
		DbHelper.close(rs,ps,conn);
	}
	return pager;
	
}


public ArrayList<Specialty> findAllM(){
	  ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
	  
	  String sql="select * from specialty";
	  
	  Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Specialty specialty=new Specialty();
				specialty.setId(rs.getInt("id"));
				specialty.setPrice(rs.getDouble("price"));
				specialty.setInventory(rs.getInt("inventory"));
				specialty.setPic(rs.getString("pic"));
				specialty.setPutawayTime(rs.getTimestamp("putaway_time"));
				specialty.setIntroduce(rs.getString("introduce"));
				specialtys.add(specialty);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close(rs, ps, conn);
		}
		return specialtys;
}



	}


